import datetime
import time
try:
    import pymssql as db
    dbdriver = 'pymssql'
except ImportError:
    import pyodbc as db
    dbdriver = 'pyodbc'

import MSLogger; log = MSLogger.get('wsgi_server')
import MSEvent
import MSDatetime

### event parsers and creators ###
def parseEventPGSM(EventTuple):
    e = {}
    e['devid']      = EventTuple[0]
    e['datetime']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
    e['type']       = EventTuple[2]
    e['id']         = EventTuple[3]
    return e
def createEventPGSM(r):
    e = MSEvent.Event()
    t = parseEventPGSM(r)
    e.fromPGSM(t)
    return e
def parseEventContactId(EventTuple):
    e = {}
    cid = ''
    for i in EventTuple[2]:
        t = str(hex(ord(i)))
        if len(t) == 3:
            t = '0%s' % (t[2])
        else:
            t = '%s%s' % (t[2], t[3])
        cid += t
    e['devid']  = EventTuple[0]
    e['value']  = cid
    e['date']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
    e['id']     = EventTuple[3]
    return e
def createEventContactId(r):
    e = MSEvent.Event()
    t = parseEventContactId(r)
    e.fromContactId(t)
    return e
def parseEventBolid(EventTuple):
    e = {}
    e['devid']      = EventTuple[0]
    e['datetime']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
    e['type']       = EventTuple[2]
    e['id']         = EventTuple[3]
    return e
def createEventBolid(r):
    e = MSEvent.Event()
    t = parseEventBolid(r)
    e.fromBolid(t)
    return e
def parseEventGPS(EventTuple):
    e = {}
    e['devid']  = EventTuple[0]
    e['date']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
    e['id']     = EventTuple[2]
    e['lat']    = EventTuple[3]
    e['lon']    = EventTuple[4]
    e['speed']  = EventTuple[5]
    return e
def createEventGPS(r):
    e = MSEvent.Event()
    t = parseEventBolid(r)
    e.fromGPSPoint(t)
    return e
def parseEventPhoto(EventTuple):
    e = {}
    e['devid']  = EventTuple[0]
    e['date']   = EventTuple[1].strftime(MSDatetime.TIME_FORMAT_MAGIC_API)
    e['id']     = EventTuple[2]
    e['image']  = EventTuple[3]
    return e
def createEventPhoto(r):
    e = MSEvent.Event()
    t = parseEventPhoto(r)
    e.fromPhoto(t)
    return e

EventCreateOperation = {
    0 : createEventPGSM,
    1 : createEventContactId,
    2 : createEventBolid,
    3 : createEventGPS,
    4 : createEventPhoto,
    5 : None
}
def createEvents(origs, opnum):
    if opnum < 0 or opnum > 4: raise RuntimeError('createEvents: wrong operation number given')
    creator = EventCreateOperation.get(opnum)
    result = []
    for o in origs:
        try:
            e = creator(o)
            result.append(e)
        except Exception,err:
            log.error('createEvents:: %s', str(err))
            continue
    return result
##################################

class Reader:
    def __init__(self, host, port, dbname, user, pwd):
        self.host = host
        self.port = port
        self.dbname = dbname
        self.user = user
        self.pwd  = pwd
        self.connection = None

    def getName(self):
        return '(MSSQL:%s:%s:%s:%s)' % (str(self.host), str(self.port), str(self.dbname), str(self.user))

    def getConnection(self):
        log.debug('%s::connecting..', self.getName())
        if dbdriver == 'pyodbc':
            return db.connect(driver='{SQL Server}', server=self.host, port=self.port, database=self.dbname, uid=self.user, pwd=self.pwd)
        if dbdriver == 'pymssql':
            return db.connect(host=self.host + ':' + self.port, database=self.dbname, user=self.user, password=self.pwd)
        raise RuntimeError('%s::getConnection: no dbdriver imported' % (self.getName()))

    def isConnected(self):
        if self.connection: return True
        return False

    def connect(self):
        if self.isConnected():
            return True

        attemptCount = 0
        while True:
            try:
                self.connection = self.getConnection()
                break
            except Exception, err:
                secs = 2
                attemptCount += 1
                if attemptCount > 3:
                    attemptCount = 0
                    secs *= 10
                log.info('%s::connection failed - sleeping %d secs', self.getName(), secs)
                time.sleep(secs)
                continue
        return True

    def getCursor(self):
        self.connect()
        return self.connection.cursor()

    def normalizeDatetime(self, d):
        return d.strftime(MSDatetime.TIME_FORMAT_MAGICDB)
    def normalizeTimerange(self, timerange):
        return (self.normalizeDatetime(timerange[0]), self.normalizeDatetime(timerange[1]))

    def getEvents_PGSM_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,type,id FROM _event_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = createEvents(data, 0)

        if len(events) > 0: obj.append(events, True)
        log.debug('%s::getEvents_PGSM_TIMED: %d events for query %s', self.getName(), len(events), query)
        return len(events)

    def getEvents_ContactId_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,contactData,eventId FROM _contactId_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = createEvents(data, 1)

        if len(events) > 0: obj.append(events, True)
        log.debug('%s::getEvents_ContactId_TIMED:: %d events for query %s', self.getName(), len(events), query)
        return len(events)

    def getEvents_Bolid_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,rs232str,eventId FROM _rs232_data WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = createEvents(data, 2)

        if len(events) > 0: obj.append(events, True)
        return len(events)

    def getEvents_GPS_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,eventId,latitude,longitude,speed FROM _gps_data_internal WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = createEvents(data, 3)

        if len(events) > 0: obj.append(events, True)
        return len(events)

    def getEvents_PHOTO_TIMED(self, obj, timerange):
        cursor      = self.getCursor()
        query       = "SELECT deviceId,eventTime,eventId,image FROM foto_arch WHERE eventTime>'%s' AND deviceId=%s" % (str(timerange[0]), str(obj.storage.meta['msId']))
        cursor.execute(query)
        data        = cursor.fetchall()
        events      = createEvents(data, 4)

        if len(events) > 0: obj.append(events, True)
        return len(events)

    def getEvents_LastHours(self, hours, opnum):
        timerange = self.normalizeTimerange(MSDatetime.getLastHours(hours))
        cursor  = self.getCursor()
        queries = [
            "SELECT deviceId,eventTime,type,id FROM _event_data_internal WHERE eventTime>'%s'" % (str(timerange[0])),
            "SELECT deviceId,eventTime,contactData,eventId FROM _contactId_data_internal WHERE eventTime>'%s'" % (str(timerange[0])),
            "SELECT deviceId,eventTime,rs232str,eventId FROM _rs232_data WHERE eventTime>'%s'" % (str(timerange[0])),
            "SELECT deviceId,eventTime,eventId,latitude,longitude,speed FROM _gps_data_internal WHERE eventTime>'%s'" % (str(timerange[0])),
            "SELECT deviceId,eventTime,eventId,image FROM foto_arch WHERE eventTime>'%s'" % (str(timerange[0])),
        ]

        events = []
        for n in range(0, 5):
            if opnum == n or (opnum == 5 and n != 4):
                cursor.execute(queries[n])
                data = cursor.fetchall()
                events += createEvents(data, n)
        return events

    def getEvents_TODAY(self, obj):
        timerange = self.normalizeTimerange(MSDatetime.getToday())
        num =   self.getEvents_PGSM_TIMED(obj, timerange)
        num +=  self.getEvents_ContactId_TIMED(obj, timerange)
        #self.getEvents_Bolid_TIMED(obj, timerange)
        #self.getEvents_GPS_TIMED(obj, timerange)
        return num

    def getEvents_TIMED(self, obj, starttime):
        timerange = self.normalizeTimerange(MSDatetime.getToday())
        timerange = (self.normalizeDatetime(starttime), timerange[1])
        num =   self.getEvents_PGSM_TIMED(obj, timerange)
        num +=  self.getEvents_ContactId_TIMED(obj, timerange)
        #self.getEvents_Bolid_TIMED(obj, timerange)
        #self.getEvents_GPS_TIMED(obj, timerange)
        return num

